﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AntiObesity.DatabaseAccess;
using System.Globalization;

namespace AntiObesity.Base.Managers
{
    public static partial class IntakeManager
    {
        public static DateTime FindLastIntakeDate(Guid userId)
        {
            using (var conn = DB.Instance.Current.GetConnection())
            {
                return DB.Instance.Current.ExecuteForDate(conn, String.Format("SELECT Max([Date]) FROM [Intakes] WHERE [UserId]='{0}'", userId));
            }
        }

        public static DateTime FindFirstIntakeDate(Guid userId)
        {
            using (var conn = DB.Instance.Current.GetConnection())
            {
                return DB.Instance.Current.ExecuteForDate(conn, String.Format("SELECT Min([Date]) FROM [Intakes] WHERE [UserId]='{0}'", userId));
            }
        }

        public static float GetTotalCalories(DateTime from, DateTime to, Guid userID)
        {
            using (var conn = DB.Instance.Current.GetConnection())
            {
                return DB.Instance.Current.ExecuteForFloat(conn, String.Format(
                    "SELECT SUM(Intakes.Quantity * Foods.Calories / 100) AS totalCalories " +
                    "FROM Foods INNER JOIN  Intakes ON Foods.ID = Intakes.FoodID " +
                    "WHERE Date>='{0}' AND Date<'{1}' AND UserID='{2}'",
                    from.ToString(CultureInfo.InvariantCulture),
                    to.ToString(CultureInfo.InvariantCulture),
                    userID));
            }
        }

        public static List<Intake> GetAll(DateTime from, DateTime to, Guid userID)
        {
            var result = new List<Intake>();
            using (var conn = DB.Instance.Current.GetConnection())
            {
                var reader = DB.Instance.Current.ExecuteForReader(conn, String.Format(
                    "SELECT * FROM [Intakes] WHERE [date]>='{0}' AND [date]<'{1}' AND [userID]='{2}'",
                    from.ToString(CultureInfo.InvariantCulture),
                    to.ToString(CultureInfo.InvariantCulture),
                    userID));
                while (reader.Read())
                {
                    result.Add(CreateIntake(reader));
                }
            }
            return result;
        }

    }
}
